package cn.btks.resultintegration.achievements.repository;

import cn.btks.resultintegration.achievements.pojo.Achievements;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author lijun
 * @Date 2020/12/16 13:28
 */
@Repository
public interface AchievementsRepository extends JpaRepository<Achievements,Long>, JpaSpecificationExecutor<Achievements> {

    /**
     *  查询某类型的所有成果数据
     * @param typeId
     * @return
     */
    @Query(value = "select * from achievements where type_id = ?1 and is_history = 0",nativeQuery = true)
    List<Achievements> findAllByTypeId(Long typeId);

    /**
     * 查询目录下的所有成果数据
     * @param catalogId
     * @return
     */
    @Query(value = "select * from achievements where catalog_id = ?1 and is_history = 0",nativeQuery = true)
    List<Achievements> findAllByCatalogId(Long catalogId);

    /**
     * 查询目录下的某类成果类型的数据
     * @param catalogId
     * @param achievementsTypeId
     * @return
     */
    @Query(value = "select * from achievements where catalog_id = ?1 and type_id = ?2 and is_history = 0",nativeQuery = true)
    List<Achievements> findAllByCatalogIdAndTypeId(Long catalogId,Long achievementsTypeId);

    /**
     * 根据ID删除成果数据
     * @param id
     * @return
     */
    @Modifying
    @Query(value = "delete from achievements where id = ?1",nativeQuery = true)
    int delete(Long id);

    /**
     * 人工审核
     * @param isApproved
     * @param verifyContent
     * @param achievementTempId
     * @param verifyDate
     * @return
     */
    @Modifying
    @Query(value = "update achievements set is_approved=?1,verify_content=?2,upload_date=?4,verify_type = 1 where id = ?3", nativeQuery = true)
    int manualVerify(Integer isApproved, String verifyContent, Long achievementTempId, Date verifyDate);

    /**
     * 更新审核人信息
     * @param verifyUser
     * @param verifyUnit
     * @param id
     * @return
     */
    @Modifying
    @Query(value = "update achievements set verify_user=?1,verify_unit=?2 where id = ?3", nativeQuery = true)
    int updateVerifyUser( String verifyUser, String verifyUnit, Long id);

    /**
     * 更新某类型的数据的是否历史数据状态
     * @param vectorTypeId
     * @param textTypeId
     * @param isHistory
     * @return
     */
    @Modifying
    @Query(value = "update achievements set is_history=?3 where type_id = ?1 or type_id = ?2", nativeQuery = true)
    int updateIsHistoryByTypeId(Long vectorTypeId,Long textTypeId,Integer isHistory);

    /**
     * 删除某类型的所有成果数据
     * @param typeId
     * @return
     */
    @Modifying
    @Query(value = "delete from achievements where type_id = ?1", nativeQuery = true)
    int deleteAllByTypeId(Long typeId);

    /***********统计查询***********/

    /**
     * 统计兵团成果申报总数及最近填报时间
     * @return
     */
    @Query(value = "SELECT upload_date as currentDate,(SELECT COUNT(*) FROM achievements) as count FROM achievements where unit_id = ?1 and is_history = 0 ORDER BY upload_date DESC",nativeQuery = true)
    List<Map<String,Object>> queryAchievementCountAndTime(Long cityId);

    /**
     * 统计师成果申报总数及最近填报时间
     * @return
     */
    @Query(value = "SELECT upload_date as currentDate,(SELECT COUNT(*) FROM achievements_temp) as count FROM achievements_temp where unit_id = ?1 and is_history = 0 ORDER BY upload_date DESC",nativeQuery = true)
    List<Map<String,Object>> queryAchievementTempCountAndTime(Long cityId);
}
